<?php


namespace app\common\service;
use app\common\service\Base;
use think\Db;

class SkuTailBill extends Base
{
    /**
     * 增加尾货单
     * @author: huanghuixiang
     * @date: 2022/9/20 9:53
     */
    public function addTailBill($parm){
        db('sku_tail_bill')->insert($parm);
    }
    /**
     * 更新尾货单
     * @author: huanghuixiang
     * @date: 2022/9/20 9:53
     */
    public function updateTailBill($where,$parm){
        db('sku_tail_bill')->where($where)->update($parm);
    }
    /**
     * 更新 order_size ->is_tail_manage
     * @author: huanghuixiang
     * @date: 2022/9/20 9:53
     */
    public function updateTailInfo($where){
        db('order_size')->where($where)->update(["is_tail_manage"=>1]);
    }
    //tab-已完成 的总数
    public function getTailDoneData($sku,$client_ordernum){
        if(!empty($sku)){
            $where[] = ['spd.sku', '=', $sku];
        }
        if(!empty($client_ordernum)){
            $where[] = ['spd.client_ordernum', '=', $client_ordernum];
        }
        $where[] = ['spd.is_get_into', '=', 1];
        $where1=$where2=$where;
        $where1[]=['spd.status','<',2];
        $where2[]=['spd.status','=',2];
        $group1='spd.client_ordernum';
        $group2='spd.sku';
        $orderby='spd.update_time';
        $field='spd.id,spd.is_reason,spd.status,spd.is_get_into,spd.reason_content,spd.update_time,spd.client_ordernum,spd.create_time,f.name as factory_name_two,sum(spd.tail_goods_dff) as total_num,sum(spd.need_put_num) as total_need_num,count(DISTINCT(spd.client_ordernum)) as  total_ordernum';
        //联合查询- 分别group by 订单号和包号，然后将数据集合一起
        $ordersql1= db('sku_tail_bill spd')
            ->join('factory f', 'spd.factory_id = f.id','left')->where($where1)->group($group1)->field($field)->order($orderby)->buildSql();
        $ordersql2 =db('sku_tail_bill spd')
            ->join('factory f', 'spd.factory_id = f.id','left')->where($where2)->group($group2)->field($field)->order($orderby)->buildSql();
        $list= Db::table($ordersql1.' aa')->union($ordersql2,true)->select();
        return count($list);;
    }
    //tab-MES尾货差异数量
    public function getMesNum($sku){
        $where0=[];
        if(!empty($sku)){
            $where[]=['t.sku','=',$sku];
        }
        $where[]=['totalnum','>',0];
        //mes库存差异 只能看到当前用户所属工厂
        if(FACTORY_IDS !='*' && FACTORY_IDS !='' && empty($factory_id) ) {
            $factoryList=explode(',',FACTORY_IDS);
            $where0[] = ['st.factory_id', 'in', $factoryList];
        }
        $field='st.sku,st.size_name,sum(available_count) as totalnum,ifnull(stm.mes_count,0) as mes_count';
        $field .=',sk.ori_pic,f.name as factory_name';
        $field .=',stm.update_time';
        $sql=db('sku_stock st')->field($field)
            ->join('sku_stock_mes stm','st.sku=stm.sku and st.size_name=stm.size_name','left')
            ->join('sku sk', 'st.sku = sk.sku','left')
            ->join('factory f', 'st.factory_id = f.id','left')
            ->where($where0)
            ->group('st.sku,st.size_name')
            ->buildSql();
         $num=DB::table($sql.' t')->where('t.totalnum <> t.mes_count')->where($where)->count();
        return  $num;
    }
    /**
     * 根据sku、尺码-获取正品库存总数
     * @date 2022年8月27日
     * author huanghuixiang
     * @param string $sku
     * @param string $size_name
     * @return int
     */
    public function getSkuStockTotal($sku, $size_name) {
        $result=db('sku_stock')->field('sum(available_count) as total_num_one')->where(['sku'=>$sku,'size_name'=>$size_name])->group("size_name")->find();
        if (empty($result)) return 0;
        return $result['total_num_one'];
    }

    /**
     * 统计入库待处理、出入待处理总数
     * @date 2022年8月29日
     * author huanghuixiang
     * @return int
     */
    public function getOutStackTotalNum($sku,$client_ordernum) {
       $result=[];
        $where='';
       if(!empty($client_ordernum))
       {
           $where .= '  o.client_ordernum="'.$client_ordernum.'" and ';
       }
        if(!empty($sku))
        {
            $where .= '  o.sku="'.$sku.'" and ';
        }
        //入库sql
       $sql1='SELECT COUNT(*) AS status FROM ((
                 SELECT * FROM ( SELECT o.sku,o.id as order_id,o.tail_over_time,t2.is_tail_manage,t2.factory_id,t2.size_name,t2.client_ordernum,t2.order_count,t2.shicai_count, t2.jiaohuo_count,t2.daohuo_count,t2.jiaohuo_tail_count,
                                       (cast(t2.shicai_count as signed) - cast(t2.order_count as signed)) as order_goods_dff,
                                        ( cast(t2.shicai_count as signed)-cast(t2.order_count*1.3 as signed)) as tail_goods_dff, t2.tail_stock_in_out,sk.stock_finished,sk.ori_pic,f.name as factory_name 
                 FROM wx_order o INNER JOIN `wx_order_size` `t2` ON `o`.`id`=`t2`.`order_id` 
                     LEFT JOIN `wx_sku` `sk` ON `o`.`sku`=`sk`.`sku` 
                     LEFT JOIN `wx_factory` `f` ON `o`.`factory_id`=`f`.`id`
                     WHERE '.$where.'  `t2`.`is_tail_manage` = 0 ) t6 WHERE `t6`.`shicai_count` > 0 AND `t6`.`order_goods_dff` > 0 AND t6.tail_goods_dff > 0 ) 
                union all ( SELECT o.sku,o.id as order_id,o.tail_over_time,t2.is_tail_manage,t2.factory_id,t2.size_name,t2.client_ordernum,t2.order_count,t2.shicai_count, t2.jiaohuo_count,t2.daohuo_count,t2.jiaohuo_tail_count,
                       (cast(t2.shicai_count as signed) - cast(t2.order_count as signed)) as order_goods_dff,
                      ( cast(t2.shicai_count as signed)-cast(t2.order_count*1.3 as signed)) as tail_goods_dff, 
                                 t2.tail_stock_in_out,sk.stock_finished,sk.ori_pic,f.name as factory_name 
                          FROM wx_order o INNER JOIN `wx_order_size` `t2` ON `o`.`id`=`t2`.`order_id`
                              LEFT JOIN `wx_sku` `sk` ON `o`.`sku`=`sk`.`sku` 
                              LEFT JOIN `wx_factory` `f` ON `o`.`factory_id`=`f`.`id` 
                     WHERE  '.$where.' `t2`.`is_tail_manage` = -1  ))
              t16';

        //出库sql
       $sql2='SELECT COUNT(*) AS status FROM (
                  SELECT t13.* from (SELECT t12.*,(select sum(st.available_count) from wx_sku_stock st where t12.sku=st.sku and t12.size_name =st.size_name GROUP BY st.size_name limit 1) as stock_count from (( SELECT * FROM ( SELECT o.sku,o.id as order_id,o.tail_over_time,t2.is_tail_manage,t2.factory_id,t2.size_name,t2.client_ordernum,t2.order_count,t2.shicai_count, t2.jiaohuo_count,t2.daohuo_count,t2.jiaohuo_tail_count,
                  (cast(t2.shicai_count as signed) - cast(t2.order_count as signed)) as order_goods_dff,
                  (cast(t2.order_count*1.3 as signed)- cast(t2.shicai_count as signed)) as tail_goods_dff, 
                  t2.tail_stock_in_out,sk.stock_finished,sk.ori_pic,f.name as factory_name FROM wx_order o INNER JOIN `wx_order_size` `t2` ON `o`.`id`=`t2`.`order_id` LEFT JOIN `wx_sku` `sk` ON `o`.`sku`=`sk`.`sku` LEFT JOIN `wx_factory` `f` ON `o`.`factory_id`=`f`.`id`
                   WHERE  '.$where.'   `t2`.`is_tail_manage` = 0 ) t6 WHERE `t6`.`shicai_count` > 0 AND `t6`.`order_goods_dff` <= 0 )) t12 
                    ) t13 
                   where t13.stock_count >0 
              union all ( SELECT o.sku,o.id as order_id,o.tail_over_time,t2.is_tail_manage,t2.factory_id,t2.size_name,t2.client_ordernum,t2.order_count,t2.shicai_count, t2.jiaohuo_count,t2.daohuo_count,t2.jiaohuo_tail_count,
                (cast(t2.shicai_count as signed) - cast(t2.order_count as signed)) as order_goods_dff,
                      ( cast(t2.shicai_count as signed)-cast(t2.order_count*1.3 as signed)) as tail_goods_dff, 
              t2.tail_stock_in_out,sk.stock_finished,sk.ori_pic,f.name as factory_name,@stock_count:=0 AS stock_count FROM wx_order o INNER JOIN `wx_order_size` `t2` ON `o`.`id`=`t2`.`order_id` LEFT JOIN `wx_sku` `sk` ON `o`.`sku`=`sk`.`sku` LEFT JOIN `wx_factory` `f` ON `o`.`factory_id`=`f`.`id`
               WHERE  '.$where.' `t2`.`is_tail_manage` = -2 )
             ) t16';

       $sqlAll= $sql1.' union all '.$sql2;
       $listCount= Db::query($sqlAll);
       $result[0]=$listCount[0]['status'];
       $result[1]=$listCount[1]['status'];
       return  $result;
    }
    /**
     * 尾货管理-判断该订单、尺码是否存在入库单、出库单中
     * 实现思路： 代码看 _PendingTailAction方法
     * @date 2022年8月29日
     * @param string $ordernum
     * @param string $size_name
     * @param string $type  -2为出库，-1为出库
     * author huanghuixiang
     * @return int
     */
    public function doesItExitDeliveryOrder($ordernum,$size_name,$type){
        //出库
        if($type==-2){
            $sql=" SELECT t10.client_ordernum,t10.size_name from 
            (
            SELECT t13.* from (SELECT t12.*,(select sum(st.available_count) from wx_sku_stock st where t12.sku=st.sku and t12.size_name =st.size_name 
            GROUP BY st.size_name limit 1) as stock_count from (
            ( SELECT * FROM ( SELECT o.sku,o.id as order_id,o.tail_over_time,t2.is_tail_manage,t2.factory_id,t2.size_name,t2.client_ordernum,t2.order_count,t2.shicai_count, t2.jiaohuo_count,t2.daohuo_count,t2.jiaohuo_tail_count,
            (cast(t2.order_count*1.3 as signed)- cast(t2.shicai_count as signed)) as tail_goods_dff, t2.tail_stock_in_out,sk.stock_finished,sk.ori_pic,f.name as factory_name FROM wx_order o INNER JOIN `wx_order_size` `t2` ON `o`.`id`=`t2`.`order_id` LEFT JOIN `wx_sku` `sk` ON `o`.`sku`=`sk`.`sku` LEFT JOIN `wx_factory` `f` ON `o`.`factory_id`=`f`.`id` WHERE   `t2`.`is_tail_manage` = 0 )
             t6 WHERE `t6`.`jiaohuo_count` > 0 AND `t6`.`tail_goods_dff` < 0 
            )) t12 ) t13  where t13.stock_count >0 
            union all 
            ( SELECT o.sku,o.id as order_id,o.tail_over_time,t2.is_tail_manage,t2.factory_id,t2.size_name,t2.client_ordernum,t2.order_count,t2.shicai_count, t2.jiaohuo_count,t2.daohuo_count,t2.jiaohuo_tail_count,(cast(t2.jiaohuo_count as signed) - cast(t2.daohuo_count as signed)) as tail_goods_dff, t2.tail_stock_in_out,sk.stock_finished,sk.ori_pic,f.name as factory_name,@stock_count:=0 AS stock_count FROM wx_order o INNER JOIN `wx_order_size` `t2` ON `o`.`id`=`t2`.`order_id`
             LEFT JOIN `wx_sku` `sk` ON `o`.`sku`=`sk`.`sku` LEFT JOIN `wx_factory` `f` ON `o`.`factory_id`=`f`.`id` WHERE  `t2`.`is_tail_manage` = ".$type."
             )
             )
             t10  where  t10.client_ordernum='".$ordernum."' and t10.size_name='".$size_name."'  limit 1";

        //入库
        }else{
            $sql="SELECT t10.client_ordernum,t10.size_name from 
           (
             (SELECT * FROM ( 
            SELECT o.sku,o.id as order_id,o.tail_over_time,t2.is_tail_manage,t2.factory_id,t2.size_name,t2.client_ordernum,t2.order_count,t2.shicai_count, t2.jiaohuo_count,t2.daohuo_count, t2.jiaohuo_tail_count,
                   ( cast(t2.shicai_count as signed)-cast(t2.order_count*1.3 as signed)) as tail_goods_dff,
                   t2.tail_stock_in_out,sk.stock_finished,sk.ori_pic,f.name as factory_name
            FROM wx_order o 
            INNER JOIN `wx_order_size` `t2` ON `o`.`id`=`t2`.`order_id` 
            LEFT JOIN `wx_sku` `sk` ON `o`.`sku`=`sk`.`sku` 
            LEFT JOIN `wx_factory` `f` ON `o`.`factory_id`=`f`.`id` WHERE   `t2`.`is_tail_manage` = 0 ) t6 
            WHERE `t6`.`shicai_count` > 0 AND `t6`.`tail_goods_dff` > 0 ) 
            union all 
            (SELECT o.sku,o.id as order_id,o.tail_over_time,t2.is_tail_manage,t2.factory_id,t2.size_name,t2.client_ordernum,t2.order_count,t2.shicai_count, t2.jiaohuo_count,t2.daohuo_count,t2.jiaohuo_tail_count,(cast(t2.jiaohuo_count as signed) - cast(t2.daohuo_count as signed)) as tail_goods_dff, t2.tail_stock_in_out,sk.stock_finished,sk.ori_pic,f.name as factory_name FROM wx_order o INNER JOIN `wx_order_size` `t2` ON `o`.`id`=`t2`.`order_id` LEFT JOIN `wx_sku` `sk` ON `o`.`sku`=`sk`.`sku` 
            LEFT JOIN `wx_factory` `f` ON `o`.`factory_id`=`f`.`id` 
            WHERE  `t2`.`is_tail_manage` = ".$type." )
         ) 
           t10  where  t10.client_ordernum='".$ordernum."' and t10.size_name='".$size_name."'  limit 1";
        }

        $resault= Db::query($sql);
        return count($resault);
    }

}